set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table  jms_dm.dm_route_area_effect_detail_dt
select
     base.in_from_area_code as in_from_area_code --始发区县编码 
    ,base.in_from_area_desc as in_from_area_desc --始发区县名称
    ,base.out_to_area_code  as out_to_area_code  --目的区县编码
    ,base.out_to_area_desc  as out_to_area_desc  --目的区县名称
    ,base.in_from_city_code as in_from_city_code --始发城市编码
    ,base.in_from_city_desc as in_from_city_desc --始发城市名称
    ,base.out_to_city_code  as out_to_city_code  --目的城市编码
    ,base.out_to_city_desc  as out_to_city_desc  --目的城市名称
    ,main_city_start.aging_area_code as in_from_aging_area_code --时效区县编码
    ,main_city_start.aging_area_desc as in_from_aging_area_desc --时效区县名称
    ,main_city_end.aging_area_code   as out_to_aging_area_code  --时效区县编码
    ,main_city_end.aging_area_desc   as out_to_aging_area_desc  --时效区县名称
    ,if(main_city_start.area_id is not null and main_city_start.is_main = 1 and
        main_city_end.area_id is not null and main_city_end.is_main = 1,1,0) as is_main_city --当始发区县-目的区县都为主城区时,则该条路由为城市-城市路由
    ,case when main_city_start.area_id is not null and main_city_start.is_main = 1 then 1 else 0 end as is_main_city_start --始发城市是否主城区
    ,case when main_city_end.area_id is not null and main_city_end.is_main = 1 then 1 else 0 end as is_main_city_end --目的城市是否主城区
    ,'{{ execution_date | cst_ds }}'
from (
    select
         in_from_area_code
        ,in_from_area_desc
        ,out_to_area_code
        ,out_to_area_desc
        ,in_from_city_code
        ,in_from_city_desc
        ,out_to_city_code
        ,out_to_city_desc
    from (
        select
             route_full_area.area_id        as in_from_area_code     
            ,route_full_area.area           as in_from_area_desc
            ,route_full_area_end.area_id    as out_to_area_code 
            ,route_full_area_end.area       as out_to_area_desc 
            ,route_full_area.city_id        as in_from_city_code
            ,route_full_area.city           as in_from_city_desc
            ,route_full_area_end.city_id    as out_to_city_code 
            ,route_full_area_end.city       as out_to_city_desc 
        from(
            select
                 city_id
                ,city
                ,area_id
                ,area
                ,aging_city_code
                ,aging_city_desc
                ,aging_area_code
                ,aging_area_desc
            from jms_dim.dim_tab_rou_main_city --区县配置表
            where is_enable=1
        ) route_full_area
        cross join(  --笛卡尔积
            select
                  city_id
                 ,city
                 ,area_id
                 ,area,aging_city_code
                 ,aging_city_desc
                 ,aging_area_code
                 ,aging_area_desc
            from jms_dim.dim_tab_rou_main_city  --区县配置表
            where is_enable=1
        ) route_full_area_end
        union all 
        select
             in_from_area_code
            ,in_from_area_desc
            ,out_to_area_code
            ,out_to_area_desc
            ,in_from_city_code
            ,in_from_city_desc
            ,out_to_city_code
            ,out_to_city_desc
        from jms_dm.dm_route_lastest_base_dt
        where dt = '{{ execution_date | cst_ds }}'
        group by in_from_area_code
                ,in_from_area_desc
                ,out_to_area_code
                ,out_to_area_desc
                ,in_from_city_code
                ,in_from_city_desc
                ,out_to_city_code
                ,out_to_city_desc
    ) a group by in_from_area_code 
                ,in_from_area_desc 
                ,out_to_area_code  
                ,out_to_area_desc  
                ,in_from_city_code 
                ,in_from_city_desc 
                ,out_to_city_code  
                ,out_to_city_desc  
) base
left join (
    select *
    from jms_dim.dim_tab_rou_main_city  --区县配置表,限制为主城区
    where is_enable = 1
) main_city_start on main_city_start.area_id=base.in_from_area_code
left join (
    select *
    from jms_dim.dim_tab_rou_main_city  --区县配置表,限制为主城区
    where is_enable = 1
) main_city_end on main_city_end.area_id=base.out_to_area_code
distribute by pmod(hash(rand()),10);

